package com.student.dao;

import com.student.model.Student;
import com.student.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDAO {

    public List<Student> findAll() {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM student ORDER BY student_number";

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setStudentNumber(rs.getString("student_number"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setAge(rs.getInt("age"));
                student.setMajor(rs.getString("major"));
                student.setClassName(rs.getString("class_name"));
                student.setAdmissionDate(rs.getDate("admission_date"));
                student.setPhone(rs.getString("phone"));
                student.setEmail(rs.getString("email"));
                student.setAddress(rs.getString("address"));
                student.setCreatedAt(rs.getTimestamp("created_at"));
                student.setUpdatedAt(rs.getTimestamp("updated_at"));
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return students;
    }

    public Student findById(int id) {
        String sql = "SELECT * FROM student WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setStudentNumber(rs.getString("student_number"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setAge(rs.getInt("age"));
                student.setMajor(rs.getString("major"));
                student.setClassName(rs.getString("class_name"));
                student.setAdmissionDate(rs.getDate("admission_date"));
                student.setPhone(rs.getString("phone"));
                student.setEmail(rs.getString("email"));
                student.setAddress(rs.getString("address"));
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean add(Student student) {
        String sql = "INSERT INTO student (student_number, name, gender, age, major, class_name, " +
                    "admission_date, phone, email, address) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, student.getStudentNumber());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getGender());
            pstmt.setInt(4, student.getAge());
            pstmt.setString(5, student.getMajor());
            pstmt.setString(6, student.getClassName());
            pstmt.setDate(7, new java.sql.Date(student.getAdmissionDate().getTime()));
            pstmt.setString(8, student.getPhone());
            pstmt.setString(9, student.getEmail());
            pstmt.setString(10, student.getAddress());

            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean update(Student student) {
        String sql = "UPDATE student SET student_number=?, name=?, gender=?, age=?, major=?, " +
                    "class_name=?, admission_date=?, phone=?, email=?, address=? WHERE id=?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, student.getStudentNumber());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getGender());
            pstmt.setInt(4, student.getAge());
            pstmt.setString(5, student.getMajor());
            pstmt.setString(6, student.getClassName());
            pstmt.setDate(7, new java.sql.Date(student.getAdmissionDate().getTime()));
            pstmt.setString(8, student.getPhone());
            pstmt.setString(9, student.getEmail());
            pstmt.setString(10, student.getAddress());
            pstmt.setInt(11, student.getId());

            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean delete(int id) {
        String sql = "DELETE FROM student WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<Student> search(String studentNumber, String name, String major) {
        List<Student> students = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM student WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (studentNumber != null && !studentNumber.trim().isEmpty()) {
            sql.append(" AND student_number LIKE ?");
            params.add("%" + studentNumber + "%");
        }
        if (name != null && !name.trim().isEmpty()) {
            sql.append(" AND name LIKE ?");
            params.add("%" + name + "%");
        }
        if (major != null && !major.trim().isEmpty()) {
            sql.append(" AND major = ?");
            params.add(major);
        }

        sql.append(" ORDER BY student_number");

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setStudentNumber(rs.getString("student_number"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setAge(rs.getInt("age"));
                student.setMajor(rs.getString("major"));
                student.setClassName(rs.getString("class_name"));
                student.setAdmissionDate(rs.getDate("admission_date"));
                student.setPhone(rs.getString("phone"));
                student.setEmail(rs.getString("email"));
                student.setAddress(rs.getString("address"));
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return students;
    }
}
